import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Ingres {
	Connection conn = null;
	Map<String, String> dataTypes = new HashMap<String, String>();
	private Properties properties = new Properties();
	private Properties bigNames = new Properties();
	
	public Connection getConn(){
		return conn;
	}
	
	public static void main(String[] args) throws SQLException, IOException {
		Ingres ingres = new Ingres();
		//ingres.test();
		ingres.generateDDL();
		
		//String strLine = "INSERT INTO RevisitMeter VALUES (15,492020,'2009-06-18 17:24:38',0,0,0,1,NULL);";
		//strLine.matches(".*\\bINTO\\b.*")
		 //Pattern p = Pattern.compile("^\\.|^\\@");

	}

	public void test() throws SQLException{
		DatabaseMetaData dbm = conn.getMetaData();
		ResultSet rsCols = dbm.getColumns("AuditApplication", "%",
				"%", "%");		
		while (rsCols.next()) {
			System.out.println(rsCols.getString(4) +" - " + rsCols.getString(6) + " - " + rsCols.getString(7));
			
		}
	}
	
	public Ingres() {
				
		try {
			properties.load(new FileInputStream("config.properties"));
			String mysqlPassword = properties.getProperty("mysqlPassword");
			
			//try {
				Class.forName("com.mysql.jdbc.Driver");
			//} catch (ClassNotFoundException e) {
			//	e.printStackTrace();
			//}
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/AuditApplication", "root",
					mysqlPassword);
			conn = connection;
			//System.out.println("### Setting Conn...");
		} catch (Exception e) {
			e.printStackTrace();
		}
		fillDataTypes();
	}

	private void generateDDL() throws SQLException, IOException {
		//System.out.println("### Init - generating DDL...");

		DatabaseMetaData dbm = conn.getMetaData();
		String catalog = "AuditApplication";
		String table = "%";
		String[] types = { "TABLE" };
		ResultSet rsTables = dbm.getTables(catalog, null, "%", types);
		
		String sql = " \\sql" + "\n set autocommit on;" + "\n \\p\\g" + "set nojournaling" + "\n \\p\\g \n";
		String sqlPk = "";
		List<String> bigNameList = new ArrayList<String>();
		while (rsTables.next()) {
			table = rsTables.getString("TABLE_NAME");
			
			ResultSet rsCols = dbm.getColumns(catalog, "%",
					table, "");
		
			String bigName="";
			if (table.length() >= 32){
				bigName = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX_BIG_NAME_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
				bigNameList.add(table); // If the column name is big, will not make diference for insert into...
			}else{
				bigName ="";				
			}
				
			
			sql = sql + "\nCREATE TABLE " + table + bigName + "(";
			while (rsCols.next()) {
				
				if (rsCols.getString(4).length() > 32){
					bigName = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX_BIG_NAME_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
				}else{
					bigName ="";
				}
				
				String fieldType = dataTypes.get(rsCols.getString(6));
				if (fieldType == null || fieldType.equals("")){
					fieldType = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX_TYPE_NOT_MAPPED:" + rsCols.getString(6);
				}
				sql = sql + "\n	"+ rsCols.getString(4) + bigName + " " + fieldType;
				if(rsCols.getString(6).equals("VARCHAR")){
					sql = sql + "(" + rsCols.getString(7) + ")";
				}					
				//## NULLABLE
				if(rsCols.getString("IS_NULLABLE").equals("NO")){
					sql = sql + " NOT NULL";
				}
				//## DEFAULT VALUE
				if(rsCols.getString(6).equals("BIT")){
					sql = sql + " DEFAULT 0";
				}
				
				sql = sql + ",";
			}//End 1 table
			sql = sql.substring(0, sql.length() - 1); //To remove the last comma
			sql = sql + "\n ); \n";
			
			
			//####### Primary Keys ######### 
			ResultSet rsPks = dbm.getPrimaryKeys(catalog, "%", table);
			String pks = "";
			while (rsPks.next()){
				pks = pks + rsPks.getString("COLUMN_NAME") + ","; 			
				//System.out.println( rsPks.getString("COLUMN_NAME") + " " + rsPks.getString("KEY_SEQ") + " " + rsPks.getString(6) );
				//System.out.println("----------------");
			}	
			if(!pks.equals("")){
				pks = pks.substring(0, pks.length() - 1);
				sqlPk =  "ALTER TABLE " + table + " ADD PRIMARY KEY (" + pks + ");";			
				sql = sql + sqlPk + "\n \\p\\g \n ";
			}
			//#### FIm PK
		}
		sql = sql +"\n \\p\\g" + "\n \\q";
		System.out.println(sql);
		conn.close();
		
		System.out.println("\n @@@@ Tables - Big names, should be lower than 32 digits @@@ \n");
		File file = new File("bigNames.properties");
		if(file.createNewFile()){
			System.out.println("File 'bigNames.properties has been created!");
		}
		bigNames.load(new FileInputStream(file));
		bigNames.clear();
		
		for (String str : bigNameList) {
			System.out.println(str);
			bigNames.setProperty(str, "");
			
		}
		bigNames.store(new FileOutputStream("bigNames.properties"), "Put names < 32 characters and run the Insert program.");
		
	}

	
	private void fillDataTypes(){
		dataTypes.put("BIT", "INTEGER1");
		dataTypes.put("INT", "INTEGER2");
		dataTypes.put("BIGINT", "INTEGER8");
		dataTypes.put("VARCHAR", "VARCHAR");
		dataTypes.put("TIMESTAMP", "TIMESTAMP");
		dataTypes.put("DATE", "TIMESTAMP");
		dataTypes.put("DATETIME", "TIMESTAMP");

		dataTypes.put("DOUBLE", "FLOAT"); //ver
		
		dataTypes.put("BLOB", "BYTE(5000)"); //ver
		dataTypes.put("FLOAT", "FLOAT"); //ver
		
	}
	
	/*
	alter table route add  FOREIGN KEY (rt_arrive_to) REFERENCES "ingres"
	.airport(ap_iatacode)
	with page_size = 8192
	\p\g
	alter table route add constraint chkroute CHECK (rt_depart_from !=
	rt_arrive_to)
	with page_size = 8192
	\p\g 
	 */
	
}
